var express = require('express');
var router = express.Router();
var Excel = require('exceljs');
const dayjs = require('dayjs')

console.log(dayjs('2018/5/21 12:23').format('YYYY/MM/DD HH:mm'));

function createHeader() {
  var workbook = new Excel.Workbook();
  var worksheet = workbook.addWorksheet("测试一", { properties: { tabColor: { argb: 'FFC0000' }, defaultRowHeight: 30, defaultColWidth: 10.99 } });
  const values = [
    ["海 流 观 测 记 录 报 表"],
    ["海区：灰鳖洋    调查船：浙普渔运18853    点位：S1    北纬：30°25′51.3″    东经：121°49′11.4″    纵坐标：3368475    横坐标：386621   "],
    ["日期", "时间", "水深", "面", "面", "0.2H", "0.2H", "0.4H", "0.4H", "0.6H", "0.6H", "0.8H", "0.8H", "底", "底", "垂线", "垂线"],
    ["日期", "时间", "", "流速", "流向", "流速", "流向", "流速", "流向", "流速", "流向", "流速", "流向", "流速", "流向", "流速", "流向"],
    ["日期", "时间", "(m)", "(m/s)", "(° )", "(m/s)", "(° )", "(m/s)", "(° )", "(m/s)", "(° )", "(m/s)", "(° )", "(m/s)", "(° )", "(m/s)", "(° )"]
  ],
    mergesArr = []
  // 先添加内容
  values.forEach(item => {
    worksheet.addRow(item)
  })
  // 合并所有该合并的
  worksheet.mergeCells("A1:Q1")
  worksheet.mergeCells("A2:O2")
  // 循环设置样式
  worksheet.eachRow((row, index) => {
    row.alignment = {
      vertical: 'middle',
      horizontal: 'center'
    }
    row.eachCell((cell, index1) => {
      cell.border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };
      if (index == 1) {
        cell.font = {
          name: "楷体_GB2312",
          size: 26,
          bold: true
        }
      } else {
        cell.font = {
          name: "宋体",
          size: 13,
          bold: true
        }
      }
    })
  })
  // 写
  workbook.xlsx.writeFile('./text1.xlsx').then(() => {
    console.log('生成excel完成')
  })
}
// createHeader()
// 排序
function create() {
  let info = [
    {
      identifier: '哈哈哈1',
      name1: '桥1',
      unit: '试试1',
      price: 13
    },
    {
      identifier: '哈哈2',
      name1: '桥2',
      unit: '试试2',
      price: 132
    }
  ]
  var workbook = new Excel.Workbook();
  var ws1 = workbook.addWorksheet("测试一", { properties: { tabColor: { argb: 'FFC0000' }, defaultRowHeight: 40, defaultColWidth: 40 } });
  ws1.addRow(['工程量汇总'])
  // ws1.mergeCells("A1: D2")
  ws1.addRow(["识别", "名字", '单位', '价钱']);

  let row = 2
  // 循环数据数组  将内容写入excel中
  for (let a of info) {
    row = row + 1
    // ws1.addRow([a.identifier, a.name1, a.unit, a.price])
    ws1.insertRow(3, [a.identifier, a.name1, a.unit, a.price])
  }
  // ws1.addRows(info)
  rowCenter(ws1, 1, row)
  colWidth(ws1, [1, 2, 3, 4, 5], 20)

  //设置　start-end　行单元格水平垂直居中/添加边框 
  function rowCenter(arg_ws, arg_start, arg_end) {
    for (i = arg_start; i <= arg_end; i++) {
      // 设置水平居中，一整个的，不必单个的
      arg_ws.findRow(i).alignment = {
        vertical: 'middle',
        horizontal: 'center'
      };
      //循环 row 中的　cell，给每个 cell添加边框 
      arg_ws.getRow(i).eachCell(function (cell, index) {
        cell.border = {
          top: { style: 'double' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };
        // cell.alignment = {
        //   vertical: 'middle', horizontal: 'center'
        // };
        cell.fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'FFCCFFCC' }
        }
      })
    }
  }
  //设置　start-end 列的宽度 
  function colWidth(arg_ws, arg_cols, arg_width) {
    for (i in arg_cols) {
      arg_ws.getColumn(arg_cols[i]).width = arg_width;
    }
  }

  workbook.xlsx.writeFile('./text1.xlsx').then(() => {
    console.log('生成excel完成')
  })
}
// create()
function replaceExcel() {
  var workbook = new Excel.Workbook()
  workbook.xlsx.readFile('./水文测验期间风速风向成果表.xlsx').then(function () {
    let ws = workbook.getWorksheet(1);
    // ws.getCell("D6").value = "12m/s"
    // ws.getCell("A6").value = "s1"
    // ws.getCell("A7").value = "s1"
    // ws.getCell("A39").value = "s2"
    ws.eachRow((row,rowIndex)=>{
      // if(rowIndex<100){
      //   let cellName="A"+rowIndex
      //   ws.getCell(cellName).value = "12m/s"
      // }
      if(row.values[1]){
        console.log(row.values);
        
      } 
      
    })
    let num = 0
    let num1 = 0
    ws.eachRow((row, rowIndex) => {
      if (row.values[1] == "测站" && ws.getRow(rowIndex + 1).values[1] == "测站") {
        if (ws.getRow(rowIndex + 2).values.length == 0 && num1 == 0) {
          num1 = rowIndex - 2
          console.log(ws.getRow(num1).values);
        }
      }
      num = rowIndex
    })
    ws.duplicateRow(num1, 2000, false);
    workbook.xlsx.writeFile('./a.xlsx').then(() => {
      console.log('重写完成')
    })
  })
}
// replaceExcel()s



function createModule() {
  let workbook = new Excel.Workbook()
  workbook.xlsx.readFile('./text.xlsx').then(() => {
    // workbook.model.sheets[0].rows[1].cells[0].value = 'ss'
    // workbook.xlsx.writeFile('./a.xlsx').then(() => {
    //   console.log('重写完成')
    // })
    let ws1 = workbook.getWorksheet("测试一")
    ws1.getRow(2).eachCell(function (cell, index) {
      console.log(cell.value);
    })

  })
}

function printJson(data) {
  console.log(JSON.stringify(data));
}


// createModule()
/* GET home page. */
router.get('/', function (req, res, next) {
  res.render('index', { title: 'Express' });
});

router.get('/test', function (req, res, next) {
  res.send({
    code:200
  })
});

module.exports = router;
